package com.ems.dao;

import com.ems.model.Employee;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDAO {
    public boolean addEmployee(Employee emp) throws SQLException {
        String sql = "INSERT INTO employees (emp_id, name, gender, nation, birthplace, birthday, email, phone, " +
                "address, dept_id, position, salary, education, graduate_school, hire_date, status, " +
                "contract_id, regular_date, leave_date) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            setEmployeeParameters(stmt, emp);
            return stmt.executeUpdate() > 0;
        }
    }

    public boolean updateEmployee(Employee emp) throws SQLException {
        String sql = "UPDATE employees SET name=?, gender=?, nation=?, birthplace=?, birthday=?, email=?, phone=?, " +
                "address=?, dept_id=?, position=?, salary=?, education=?, graduate_school=?, hire_date=?, " +
                "status=?, contract_id=?, regular_date=?, leave_date=? WHERE emp_id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            setEmployeeParameters(stmt, emp);
            stmt.setString(19, emp.getEmpId());
            return stmt.executeUpdate() > 0;
        }
    }

    public boolean deleteEmployee(String empId) throws SQLException {
        String sql = "DELETE FROM employees WHERE emp_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, empId);
            return stmt.executeUpdate() > 0;
        }
    }

    public Employee getEmployeeById(String empId) throws SQLException {
        String sql = "SELECT * FROM employees WHERE emp_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, empId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return getEmployeeFromResultSet(rs);
                }
            }
        }
        return null;
    }

    public List<Employee> getAllEmployees() throws SQLException {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT * FROM employees";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                employees.add(getEmployeeFromResultSet(rs));
            }
        }
        return employees;
    }

    public List<Employee> searchEmployees(String keyword) throws SQLException {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT * FROM employees WHERE emp_id LIKE ? OR name LIKE ? OR dept_id IN " +
                "(SELECT dept_id FROM departments WHERE dept_name LIKE ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            String searchTerm = "%" + keyword + "%";
            stmt.setString(1, searchTerm);
            stmt.setString(2, searchTerm);
            stmt.setString(3, searchTerm);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    employees.add(getEmployeeFromResultSet(rs));
                }
            }
        }
        return employees;
    }

    private void setEmployeeParameters(PreparedStatement stmt, Employee emp) throws SQLException {
        stmt.setString(1, emp.getEmpId());
        stmt.setString(2, emp.getName());
        stmt.setString(3, emp.getGender());
        stmt.setString(4, emp.getNation());
        stmt.setString(5, emp.getBirthplace());
        stmt.setDate(6, new java.sql.Date(emp.getBirthday().getTime()));
        stmt.setString(7, emp.getEmail());
        stmt.setString(8, emp.getPhone());
        stmt.setString(9, emp.getAddress());
        stmt.setInt(10, emp.getDeptId());
        stmt.setString(11, emp.getPosition());
        stmt.setDouble(12, emp.getSalary());
        stmt.setString(13, emp.getEducation());
        stmt.setString(14, emp.getGraduateSchool());
        stmt.setDate(15, new java.sql.Date(emp.getHireDate().getTime()));
        stmt.setString(16, emp.getStatus());
        stmt.setString(17, emp.getContractId());
        stmt.setDate(18, emp.getRegularDate() != null ? new java.sql.Date(emp.getRegularDate().getTime()) : null);
        stmt.setDate(19, emp.getLeaveDate() != null ? new java.sql.Date(emp.getLeaveDate().getTime()) : null);
    }

    private Employee getEmployeeFromResultSet(ResultSet rs) throws SQLException {
        Employee emp = new Employee();
        emp.setEmpId(rs.getString("emp_id"));
        emp.setName(rs.getString("name"));
        emp.setGender(rs.getString("gender"));
        emp.setNation(rs.getString("nation"));
        emp.setBirthplace(rs.getString("birthplace"));
        emp.setBirthday(rs.getDate("birthday"));
        emp.setEmail(rs.getString("email"));
        emp.setPhone(rs.getString("phone"));
        emp.setAddress(rs.getString("address"));
        emp.setDeptId(rs.getInt("dept_id"));
        emp.setPosition(rs.getString("position"));
        emp.setSalary(rs.getDouble("salary"));
        emp.setEducation(rs.getString("education"));
        emp.setGraduateSchool(rs.getString("graduate_school"));
        emp.setHireDate(rs.getDate("hire_date"));
        emp.setStatus(rs.getString("status"));
        emp.setContractId(rs.getString("contract_id"));
        emp.setRegularDate(rs.getDate("regular_date"));
        emp.setLeaveDate(rs.getDate("leave_date"));
        return emp;
    }
}